<?php
/**
 * SQL Export
 * @author Tomas Srna
 * @package bakalarka
 */

require('../bootstrap.php');

// UserModel
echo UserModel::struct()->sqlStructure()."\n";

// GroupModel
echo GroupModel::struct()->sqlStructure()."\n";

// TokenModel
echo TokenModel::struct()->sqlStructure()."\n";

// AclModel
echo AclModel::struct()->sqlStructure()."\n";

// LogModel
echo LogModel::struct()->sqlStructure()."\n";

// DomainModel
echo DomainModel::struct()->sqlStructure()."\n";

// HostingModel
echo HostingModel::struct()->sqlStructure()."\n";

// DnsRecordModel
echo DnsRecordModel::struct()->sqlStructure()."\n";

// HostingFtpModel
echo HostingFtpModel::struct()->sqlStructure()."\n";

// HostingMysqlModel
echo HostingMysqlModel::struct()->sqlStructure()."\n";

// --------- DATA ---------

// User DATA
echo UserModel::create(array(
	'username' => "admin",
	'password_hash' => hash(Config::$hash_function, "adminpass"),
	'email' => "admin@srna.sk",
	'group' => "admin",
	))->sql()."\n";
echo UserModel::create(array(
	'username' => "tomas",
	'password_hash' => hash(Config::$hash_function, "tomaspass"),
	'email' => "tomas@srna.sk",
	'group' => "user",
	))->sql()."\n";

// Group DATA
echo GroupModel::create(array('groupname' => "admin"))->sql()."\n";
echo GroupModel::create(array('groupname' => "user"))->sql()."\n";

// Acl DATA

echo AclModel::create(array('role' => "@anonymous", 'resource' => "login", 'allow' => 1))->sql()."\n";
echo AclModel::create(array('role' => "@anonymous", 'resource' => "action", 'allow' => 1))->sql()."\n";
echo AclModel::create(array('role' => "@admin", 'resource' => "*", 'allow' => 1))->sql()."\n";

echo AclModel::create(array('role' => "@user", 'resource' => "login", 'allow' => 0))->sql()."\n";
echo AclModel::create(array('role' => "@admin", 'resource' => "login", 'allow' => 0))->sql()."\n";

echo AclModel::create(array('role' => "@user", 'resource' => "index", 'allow' => 1))->sql()."\n";
echo AclModel::create(array('role' => "@user", 'resource' => "action", 'allow' => 1))->sql()."\n";
echo AclModel::create(array('role' => "@user", 'resource' => "domain", 'allow' => 1))->sql()."\n";
echo AclModel::create(array('role' => "@user", 'resource' => "hosting", 'allow' => 1))->sql()."\n";
//echo AclModel::create(array('role' => "@user", 'resource' => "log", 'allow' => 1))->sql()."\n";
//echo AclModel::create(array('role' => "@user", 'resource' => "backup", 'allow' => 1))->sql()."\n";

//echo AclModel::create(array('role' => "tomas", 'resource' => "domain:srna.sk", 'allow' => 1))->sql()."\n";
//echo AclModel::create(array('role' => "tomas", 'resource' => "hosting:srna_sk", 'allow' => 1))->sql()."\n";

// --------- Stored procedures & Views ---------
?>

-- DNS
DELIMITER //
CREATE FUNCTION `get_domain_name`(`domain_id` INT)
	RETURNS varchar(50)
	LANGUAGE SQL
	NOT DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY INVOKER
	COMMENT 'Ziska meno domeny z ID'
BEGIN
	declare domain_name varchar(50);
	select name into domain_name from domain where id = domain_id;
	return domain_name;
END//
CREATE FUNCTION `transform_dns`(`input` VARCHAR(50), `domain_name` VARCHAR(50))
	RETURNS varchar(50)
	LANGUAGE SQL
	NOT DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY INVOKER
	COMMENT 'Transformuje standardny DNS nazov na format pre PowerDNS'
BEGIN
	declare transformed varchar(50);
	select 
	if(
		(input = '@'),
		domain_name,
		if(
			(substr(input,-(1)) = '.'),
			substr(input,1,(char_length(input) - 1)),
			concat(input,'.',domain_name)
		)
	) into transformed;
	return(transformed);
END//
DELIMITER ;
CREATE SQL SECURITY INVOKER VIEW `pdns_record` AS select if((`dns_record`.`type` = 'SOA'),concat(`dns_record`.`primary_ns`,' ',`dns_record`.`resp_person`,' ',`dns_record`.`serial`,' ',`dns_record`.`refresh`,' ',`dns_record`.`retry`,' ',`dns_record`.`expire`,' ',`dns_record`.`default_ttl`),`transform_dns`(if(((`dns_record`.`type` = 'A') or (`dns_record`.`type` = 'AAAA') or (`dns_record`.`type` = 'SOA') or (`dns_record`.`type` = 'SPF') or (`dns_record`.`type` = 'TXT')),concat(`dns_record`.`content`,'.'),`dns_record`.`content`),`get_domain_name`(`dns_record`.`domain_id`))) AS `content`,`dns_record`.`ttl` AS `ttl`,`dns_record`.`mx_priority` AS `prio`,`dns_record`.`type` AS `qtype`,`dns_record`.`domain_id` AS `domain_id`,`transform_dns`(`dns_record`.`name`,`get_domain_name`(`dns_record`.`domain_id`)) AS `name` from `dns_record` where ((select `domain`.`active` from `domain` where (`domain`.`id` = `dns_record`.`domain_id`)) = 1);

-- FTP
CREATE TABLE IF NOT EXISTS `proftpd_group` (
  `groupname` varchar(50) NOT NULL,
  `gid` int(11) NOT NULL,
  `members` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `proftpd_group` (`groupname`, `gid`, `members`) VALUES
	('www-data', 33, '');
CREATE SQL SECURITY INVOKER VIEW `proftpd_quota_limit` AS select `ftp`.`username` AS `name`,'user' AS `quota_type`,'false' AS `per_session`,'hard' AS `limit_type`,(((select `hosting`.`space` from `hosting` where (`hosting`.`id` = `ftp`.`hosting_id`)) * 1024) * 1024) AS `bytes_in_avail`,0 AS `bytes_out_avail`,0 AS `bytes_xfer_avail`,0 AS `files_in_avail`,0 AS `files_out_avail`,0 AS `files_xfer_avail` from `ftp`;
CREATE SQL SECURITY INVOKER VIEW `proftpd_quota_tally` AS select `ftp`.`username` AS `name`,'user' AS `quota_type`,(((select `hosting`.`used` from `hosting` where (`hosting`.`id` = `ftp`.`hosting_id`)) * 1024) * 1024) AS `bytes_in_used`,0 AS `bytes_out_used`,0 AS `bytes_xfer_used`,0 AS `files_in_used`,0 AS `files_out_used`,0 AS `files_xfer_used` from `ftp`;
CREATE SQL SECURITY INVOKER VIEW `proftpd_user` AS select `ftp`.`username` AS `userid`,`ftp`.`password_hash` AS `passwd`,(2000 + `ftp`.`hosting_id`) AS `uid`,(select `proftpd_group`.`gid` from `proftpd_group` where (`proftpd_group`.`groupname` like 'www-data')) AS `gid`,(select concat(`hosting`.`root`,'/',coalesce(`ftp`.`root`,'')) from `hosting` where (`hosting`.`id` = `ftp`.`hosting_id`)) AS `homedir`,'/bin/false' AS `shell` from `ftp` where ((select `hosting`.`active` from `hosting` where (`hosting`.`id` = `ftp`.`hosting_id`)) = 1);
DELIMITER //
CREATE DEFINER=`root`@`%` FUNCTION `proftpd_getspace`(`username` varchar(50)) RETURNS int(11)
    SQL SECURITY INVOKER
    COMMENT 'Vracia v MB'
BEGIN
	declare s int(11);
	select `space` into s from hosting where id = (select hosting_id from ftp where ftp.username = username);
	return s;
END//
CREATE DEFINER=`root`@`%` FUNCTION `proftpd_getused`(`username` varchar(50)) RETURNS int(11)
    SQL SECURITY INVOKER
    COMMENT 'Vracia v MB'
BEGIN
	declare u int(11);
	select `used` into u from hosting where id = (select hosting_id from ftp where ftp.username = username);
	return u;
END//
DELIMITER ;

-- MySQL
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `mysql_create`(IN `name` VARCHAR(50))
BEGIN
	IF NOT (mysql_db_exists(name) > 0 OR mysql_user_exists(name) > 0) THEN
		-- Vytvorime databazu (trochu tricky, potrebujeme dostat string do varname)
		SET @dbname = name;
		SET @sql_text = concat('CREATE DATABASE IF NOT EXISTS ', @dbname, '');
		PREPARE stmt FROM @sql_text;
		EXECUTE stmt;
		-- Vytvorime uzivatela
		SET @sql_text = concat('CREATE USER \'', name, '\'@\'localhost\'');
		PREPARE stmt FROM @sql_text;
		EXECUTE stmt;
		
		SET @sql_text = concat('GRANT USAGE ON *.* TO \'', name, '\'@\'localhost\'');
		PREPARE stmt FROM @sql_text;
		EXECUTE stmt;
		
		SET @sql_text = concat('GRANT ALL PRIVILEGES ON ', @dbname, '.* TO \'', name, '\'@\'localhost\'');
		PREPARE stmt FROM @sql_text;
		EXECUTE stmt;
		
		FLUSH PRIVILEGES;
	END IF;
END//
CREATE DEFINER=`root`@`localhost` FUNCTION `mysql_db_exists`(`name` VARCHAR(50)) RETURNS tinyint(1)
BEGIN
	declare r tinyint(1);
	select count(*) into r from information_schema.SCHEMATA where information_schema.SCHEMATA.SCHEMA_NAME = name;
	return r;
END//
CREATE DEFINER=`root`@`localhost` PROCEDURE `mysql_delete`(IN `name` VARCHAR(50))
BEGIN
	-- Vytvorime databazu (trochu tricky, potrebujeme dostat string do varname)
	SET @dbname = name;
	SET @sql_text = concat('DROP DATABASE ', @dbname, '');
	PREPARE stmt FROM @sql_text;
	EXECUTE stmt;
	-- Vytvorime uzivatela
	SET @sql_text = concat('DROP USER \'', name, '\'@\'localhost\'');
	PREPARE stmt FROM @sql_text;
	EXECUTE stmt;
	
	FLUSH PRIVILEGES;
END//
CREATE DEFINER=`root`@`localhost` PROCEDURE `mysql_passwd`(IN `name` VARCHAR(50), IN `passwd` VARCHAR(50))
BEGIN
	SET @sql_text = concat('SET PASSWORD FOR \'', name, '\'@\'localhost\' = PASSWORD(\'', passwd, '\')');
	PREPARE stmt FROM @sql_text;
	EXECUTE stmt;
	FLUSH PRIVILEGES;
END//
CREATE DEFINER=`root`@`localhost` FUNCTION `mysql_user_exists`(`name` VARCHAR(50)) RETURNS tinyint(1)
BEGIN
	declare r tinyint(1);
	select count(*) into r from mysql.user where mysql.user.User = name;
	return r;
END//
DELIMITER ;
<?php

// DEBUG Selector
/*
Selector::select('*')->from(AclModel::struct())
	->where('role = ?', "@admin")->order("allow asc")
	->limit(0,2)->fetch()->dump();
echo "\n";
Selector::select('*')->from("acl")
	->where('role = ?', "@admin")->order("allow asc")
	->limit(0,2)->fetch()->dump();
echo "\n";
*/
